﻿#region Using Directives

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using GeekRule.ORM.Entities;
using GeekRule.ORM.Query;

#endregion

namespace GeekRule.ORM
{
    // TODO: Transaction handling needs refactored and completed.

    /// <summary>
    /// Provides base query capabilities for common, reusable query logic and for both strongly-typed and 
    /// ADO.NET built-in data results
    /// </summary>
    /// <example>
    /// Usage example:
    ///   using (QueryManager qm = new QueryManager(DataContextType.MicrosoftSqlServer, "myConnectionStringName"))
    ///   {
    ///       MyEntity entity = qm.ExecuteQuery&lt;MyEntity&gt;("dbo.mystoredproc").SingleOrDefault();
    ///   }
    /// </example>
    public class QueryManager : IQueryManager
    {
        #region Private Members

        private readonly IDataContext _context;
        private IQueryAdapter _queryAdapter;

        #endregion

        #region Public Accessors

        /// <summary>
        /// DataContext type for this instance
        /// </summary>
        public IDataContext Context
        {
            get { return _context; }
        }

        #endregion

        #region Constructors

        public QueryManager()
        {
        }

        public QueryManager(IDataContext dataContext)
        {
            _context = dataContext;
        }

        /// <summary>
        /// Instantiates the QueryManager and sets up the DataContext
        /// </summary>
        /// <param name="contextType"></param>
        /// <param name="connectionStringName"></param>
        public QueryManager(DataContextType contextType, string connectionStringName)
        {
            if (contextType == DataContextType.MicrosoftSqlServer)
            {
                _context = new MsSqlDataContext(connectionStringName);
            }
            else
            {
                throw new NotImplementedException("QueryManager not implemented for: " + contextType);
            }
        }

        #endregion

        #region Public Methods

        #region ADO.NET Queries

        #region DataSet Queries
        
        /// <summary>
        /// Executes a query andd returns the results as a DataSet
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbCommand command)
        {
            IQueryAdapter queryAdapter = GetQueryAdapter();
            return queryAdapter.ExecuteDataSet(command);
        }

        /// <summary>
        /// Executes a transactional query andd returns the results as a DataSet
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbTransaction transaction, DbCommand command)
        {
            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteDataSet(command);
        }

        /// <summary>
        /// Executes a query andd returns the results as a DataSet
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(CommandType commandType, string commandText, params object[] parameterValues)
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);
            return ExecuteDataSet(command);
        }

        /// <summary>
        /// Executes a transactional query andd returns the results as a DataSet
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbTransaction transaction, CommandType commandType, string commandText, params object[] parameterValues)
        {
            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteDataSet(commandType, commandText, parameterValues);
        }

        /// <summary>
        /// Executes a query andd returns the results as a DataSet
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);
            return ExecuteDataSet(command);
        }

        /// <summary>
        /// Executes a transactional query andd returns the results as a DataSet
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbTransaction transaction, string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteDataSet(command);
        }

        #endregion

        #region Non-Query Queries

        /// <summary>
        /// Executes a query returning no results
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbCommand command)
        {
            IQueryAdapter queryAdapter = GetQueryAdapter();
            return queryAdapter.ExecuteNonQuery(command);
        }

        /// <summary>
        /// Executes a transactional query returning no results
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbTransaction transaction, DbCommand command)
        {
            IQueryAdapter queryAdapter = GetQueryAdapter();

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return queryAdapter.ExecuteNonQuery(command);
        }

        /// <summary>
        /// Executes a query returning no results
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText, params object[] parameterValues)
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);
            return ExecuteNonQuery(command);
        }

        /// <summary>
        /// Executes a transactional query returning no results
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbTransaction transaction, CommandType commandType, string commandText, params object[] parameterValues)
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteNonQuery(command);
        }

        /// <summary>
        /// Executes a query returning no results
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);
            return ExecuteNonQuery(command);
        }

        /// <summary>
        /// Executes a transactional query returning no results
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbTransaction transaction, string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteNonQuery(command);
        }

        #endregion

        #region IDataReader Queries
        
        /// <summary>
        /// Executes a query returning results as IDataReader
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            DbCommand command = GetCommand(commandType, commandText);
            return ExecuteReader(command);
        }

        /// <summary>
        /// Executes a transactional query returning results as IDataReader
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(DbCommand command)
        {
            IQueryAdapter queryAdapter = GetQueryAdapter();
            return queryAdapter.ExecuteReader(command);
        }

        /// <summary>
        /// Executes a transactional query returning results as IDataReader
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(DbTransaction transaction, DbCommand command)
        {
            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteReader(command);
        }

        /// <summary>
        /// Executes a query returning results as IDataReader
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(CommandType commandType, string commandText, params object[] parameterValues)
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);
            return ExecuteReader(command);
        }

        /// <summary>
        /// Executes a transactional query returning results as IDataReader
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(DbTransaction transaction, CommandType commandType, string commandText, params object[] parameterValues)
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteReader(command);
        }

        /// <summary>
        /// Executes a query returning results as IDataReader
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);
            return ExecuteReader(command);
        }

        /// <summary>
        /// Executes a transactional query returning results as IDataReader
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(DbTransaction transaction, string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteReader(command);
        }

        #endregion

        #region Scalar Queries

        /// <summary>
        /// Executes a query returning results as a scalar value
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public object ExecuteScalar(DbCommand command)
        {
            IQueryAdapter queryAdapter = GetQueryAdapter();
            return queryAdapter.ExecuteScalar(command);
        }

        /// <summary>
        /// Executes a transactional query returning results as a scalar value
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public object ExecuteScalar(DbTransaction transaction, DbCommand command)
        {
            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteScalar(command);
        }

        /// <summary>
        /// Executes a query returning results as a scalar value
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType commandType, string commandText, params object[] parameterValues)
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);
            return ExecuteScalar(command);
        }

        /// <summary>
        /// Executes a transactional query returning results as a scalar value
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public object ExecuteScalar(DbTransaction transaction, CommandType commandType, string commandText, params object[] parameterValues)
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteScalar(command);
        }

        /// <summary>
        /// Executes a query returning results as a scalar value
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public object ExecuteScalar(string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);
            return ExecuteScalar(command);
        }

        /// <summary>
        /// Executes a transactional query returning results as a scalar value
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public object ExecuteScalar(DbTransaction transaction, string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteScalar(command);
        }

        #endregion

        #endregion

        #region Strongly-Typed Queries

        /// <summary>
        /// Executes a query returning strongly-typed results
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(DbCommand command) where T : EntityBase
        {
            IQueryAdapter queryAdapter = GetQueryAdapter();
            return queryAdapter.ExecuteQuery<T>(command);
        }

        /// <summary>
        /// Executes a transactional query returning strongly-typed results
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="transaction"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(DbTransaction transaction, DbCommand command) where T : EntityBase
        {
            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteQuery<T>(command);
        }

        /// <summary>
        /// Executes a query returning strongly-typed results
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(string storedProcedureName, params object[] parameterValues) where T : EntityBase
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);
            return ExecuteQuery<T>(command);
        }

        /// <summary>
        /// Executes a transactional query returning strongly-typed results
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="transaction"></param>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(DbTransaction transaction, string storedProcedureName, params object[] parameterValues) where T : EntityBase
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteQuery<T>(command);
        }

        /// <summary>
        /// Executes a query returning strongly-typed results
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(CommandType commandType, string commandText, params object[] parameterValues) where T : EntityBase
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);
            return ExecuteQuery<T>(command);
        }

        /// <summary>
        /// Executes a transactional query returning strongly-typed results
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(DbTransaction transaction, CommandType commandType, string commandText, params object[] parameterValues) where T : EntityBase
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            return ExecuteQuery<T>(command);
        }

        #endregion

        #region Multi-Result Queries

        /// <summary>
        /// Executes a multi-result query
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public void ExecuteMultiResultQuery(DbCommand command)
        {
            IQueryAdapter queryAdapter = GetQueryAdapter();
            queryAdapter.ExecuteMultiResultQuery(command);
        }

        /// <summary>
        /// Executes a multi-result query
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="transaction"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public void ExecuteMultiResultQuery(DbTransaction transaction, DbCommand command)
        {
            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            ExecuteMultiResultQuery(command);
        }

        /// <summary>
        /// Executes a multi-result query
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public void ExecuteMultiResultQuery(string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);
            ExecuteMultiResultQuery(command);
        }

        /// <summary>
        /// Executes a multi-result query
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="transaction"></param>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public void ExecuteMultiResultQuery(DbTransaction transaction, string storedProcedureName, params object[] parameterValues)
        {
            DbCommand command = GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            ExecuteMultiResultQuery(command);
        }

        /// <summary>
        /// Executes a multi-result query
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public void ExecuteMultiResultQuery(CommandType commandType, string commandText, params object[] parameterValues)
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);
            ExecuteMultiResultQuery(command);
        }

        /// <summary>
        /// Executes a multi-result query
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public void ExecuteMultiResultQuery(DbTransaction transaction, CommandType commandType, string commandText, params object[] parameterValues)
        {
            DbCommand command = GetCommand(commandType, commandText, parameterValues);

            if (!Context.HasDbTransaction)
            {
                Context.Transaction = transaction;
            }

            ExecuteMultiResultQuery(command);
        }

        public List<T> GetResult<T>() where T : EntityBase
        {
            if (_queryAdapter == null)
            {
                throw new Exception("Query adapter is null.  Ensure ExecuteMultiResultQuery was called first.");
            }

            return _queryAdapter.GetResult<T>();
        }
        #endregion

        #region Command Builders

        /// <summary>
        /// Creates a DbCommand of the specified type and command text.  
        /// If optional parameterValues are passed, the parameters are auto-created and assigned values.
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public DbCommand GetCommand(CommandType commandType, string commandText, params object[] parameterValues)
        {
            IQueryAdapter queryAdapter = GetQueryAdapter();
            return queryAdapter.GetCommand(commandType, commandText, parameterValues);
        }

        /// <summary>
        /// Creates a Text type DbCommand
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public DbCommand GetSqlStringCommand(string query, params object[] parameterValues)
        {
            return GetCommand(CommandType.Text, query, parameterValues);
        }

        /// <summary>
        /// Creates a StoredProc type DbCommand
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public DbCommand GetStoredProcedureCommand(string storedProcedureName, params object[] parameterValues)
        {
            return GetCommand(CommandType.StoredProcedure, storedProcedureName, parameterValues);
        }

        #endregion

        #region Dispose

        public void Dispose()
        {
            // TODO: implement disposal logic
        }

        #endregion

        #endregion

        #region Private Helpers

        /// <summary>
        /// Returns an IQueryAdapter corresponding to the DataContext type.
        /// </summary>
        /// <returns></returns>
        private IQueryAdapter GetQueryAdapter()
        {
            if (_queryAdapter == null)
            {
                if (_context.ContextType == DataContextType.MicrosoftSqlServer)
                {
                    if (_context.Transaction == null)
                    {
                        _queryAdapter = new MsSqlQueryAdapter((SqlConnection)_context.Connection);
                    }
                    else
                    {
                        _queryAdapter = new MsSqlQueryAdapter((SqlConnection)_context.Connection, (SqlTransaction)_context.Transaction);
                    }
                }
                else
                {
                    throw new NotImplementedException("IQueryAdapter does not have an implementation for: " + _context.ContextType);
                }
            }

            return _queryAdapter;
        }

        #endregion

    }
}